#region Imported Namespaces
using System;
using System.Data;
using System.Collections;
using System.Data.SqlClient;
using PHSRAG.Insight21.InsightUtilities;
using System.Text;

using BO					= PHSRAG.Insight21.BusinessObjects;
using DataAccess			= PHSRAG.Utility.DataAccess;
using ICachePolicy			= PHSRAG.Insight21.Policy.ICachePolicy;
using SearchCriteria		= PHSRAG.Insight21.SearchContext.SearchCriteria;
using AgreementConstants	= PHSRAG.Insight21.Agreements.AgreementConstants;
using GenerateKey = PHSRAG.Insight21.BusinessObjects.ModuleKey;
#endregion

namespace PHSRAG.Insight21.Agreements
{
	/// <summary>
	/// SalaryDistributions Class retrieves the Salary Distributions resultset and 
	/// is used to sort, filter and calculate Total$ like TotalAnnualRate, TotalMonthlyRate,
	/// TotalCommittedSalary, TotalCommittedFringe, TotalTotalCommitted and TotalPaidToDate.  
	/// It returns a resultset made up of 2 tables.  
	///		SalaryDistributions  and 
	///		2nd contains the totals.
	/// </summary>
	public class SalaryDistributions : BO.BusinessObject
	{
		#region Constants
		private static string SalaryDistributionsKey		= GenerateKey.MakeKey("SalaryDistributions_6472C46B-85E6-4031-B02B-525C70B28F59",GenerateKey.Agreement);
		private const string GetSalariesOverviewStoreProc	= "GetSalariesOverview";
      private const string GetSalaryDetailsStoreProc	= "GetSalaryDetails";

		public static string FilteredDataTableName			= "filtered";
		private const string DefaultSortColumn				= "LastName";
		private const string OriginalDataTableName			= "original";
		public const string TotalsTableName					= "Totals";
		
		private const string SumAnnualRate					= "sum(AnnualRate)";
		private const string SumMonthlyRate					= "sum(MonthlyRate)";
		private const string SumCommittedSalary				= "sum(CommittedSalary)";
		private const string SumCommittedFringe				= "sum(CommittedFringe)";
		private const string SumTotalCommitted				= "sum(TotalCommitted)";
		private const string SumPaidToDate					= "sum(PaidToDate)";

		private const string SessionID					= "@sessionID";
		private const string DomainUserID				= "@domainUserID";
		private const string IncludeProgramProjects		= "@includeProgramProjects";
		private const string FundNumber					= "@fundNumber";
		private const string FolderNumber				= "@folderNumber";
		private const string BicsNumber					= "@bicsNumber";
		private const string WalkerNumber				= "@walkerNumber";
		private const string SponsorNumber				= "@sponsorNumber";
		private const string SponsorName				= "@sponsorName";
		private const string SponsorTypeId				= "@sponsorTypeId";
		private const string AgreementTypeId			= "@agreementTypeId";
		private const string ActivityCode				= "@activityCode";
		private const string LocationId					= "@locationId";
		private const string BudgetStartDate			= "@budgetStartDate";
		private const string BudgetEndDate				= "@budgetEndDate";
		private const string ProjectStartDate			= "@projectStartDate";
		private const string ProjectEndDate				= "@projectEndDate";
		private const string PiId						= "@piId";
		//private const string LabId					= "@labId";
		private const string ChiefCodeList				= "@chiefCodeList";
		private const string SponsorGroupTypeId			= "@sponsorGroupTypeId";
		private const string BudgetEndingDays			= "@budgetEndingDays";
		private const string ProjectEndingDays			= "@projectEndingDays";
		private const string DaysFundCreated			= "@fundCreatedDays";
		private const string LowBalance					= "@lowBalance";
		private const string HighBalance				= "@highBalance";
		private const string AgreementStatusTypeList	= "@agreementStatusTypeList";
		private const string FundStatusTypeId			= "@FundStatusTypeId";
		private const string Currentdatetime			= "@Currentdatetime";
		private const string FirstName					= "@firstName";
		private const string LastName					= "@lastName";
		private const string LastGLFeedRunDate			= "@lastGLFeedRunDate";
		private const string EmployeeID					= "@employeeID";
		private const string PayDateFrom				= "@payDateFrom";
		private const string PayDateTo					= "@payDateTo";
		private const string IncomingFundNumber			= "@incomingFundNumber";
        private const string Cumulative = "@cumulative";
        private const string BudgetYear = "@budgetYear";
        #endregion
		
		#region private type (Enum)
		/// <summary>
		/// The StoredPROCArguments enumeration defines values for indexing the stored procedure 
		/// arguments and total number of arguments passed to the stored procedure
		/// </summary>		
		private enum StoredPROCArguments 
		{
			SessionID,
			SearchCriteria,
			LastGLFeedRunDate,
			IncomingFundNumber,
			IgnoreFundStatus,
			IgnoreAgreementStatus,
            IsDetail,
            CumLevel,
            BudgetYear
        }

		#endregion
        
		#region Instance Variables
		/// <summary>
		/// Private Instance variables used for sorting dataview.
		/// </summary>
		private Hashtable		salaryDistributionMap	= new Hashtable();
		private string			currentDataTableName	= OriginalDataTableName;
		private bool			sortAscending			= true;
		private string			sortColumn				= DefaultSortColumn;	// Keep this in sync with what the stored proc orders by.
		
		#endregion

		#region Constructor
		/// <summary>
		/// Initializes the base class with the specified cache policy and 
		/// also initialize the DataContainer
		/// </summary>
		/// <param name="cachePolicy">Page's cache policy.</param>
		public SalaryDistributions() : base(null) 
		{
		}
		#endregion

		#region Properties
		/// <summary>
		/// Get the key to be used in all caching operations for this instance of the object.
		/// </summary>
		public static string CacheKey	{ get { return SalaryDistributionsKey; } }

		/// <summary>
		/// Get the total number of Salary Distributions.
		/// </summary>
		public int Count
		{
			get {  return (DataContainer.Tables.Count == 0) ? 0 : DataContainer.Tables[OriginalDataTableName].Rows.Count; }
		}
		  
		/// <summary>
		/// Get the sort sequence.
		/// </summary>
		public bool SortAscending
		{
			get { return sortAscending; }
		}

		/// <summary>
		/// Get the name of the sort column.
		/// </summary>
		public string SortColumn
		{
			get { return sortColumn; }
		}	
		
		#endregion

		#region Methods
		/// <summary>
		/// Sort the list of Salary Distributions by the speicified column.
		/// If the column is to be sorted is the same as that of last sorted on, 
		/// the sort sequence is toggled. Else, the
		/// sort sequence is set to ascending. the routine extracts 
		/// the tables(Salary Distributions information table and Totals Table) from the DataContainer and 
		/// Salary Distributions is sorted using Dataview sort method.
		/// Orginal table is removed and sorted table is included back
		/// </summary>
		/// <param name="columnName">Column to sort by</param>
		public void Sort(string columnName)
		{

			salaryDistributionMap.Clear();

			sortAscending = (sortColumn == columnName) ? !sortAscending : true;
			sortColumn = columnName;

			DataTable dataTable = DataContainer.Tables[currentDataTableName];
			DataView dataView = dataTable.DefaultView;
			dataView.Sort = string.Format("{0} {1}", columnName, sortAscending ? Constants.SortViewAscending: Constants.SortViewDescending);

			DataTable sortedDataTable = dataTable.Clone();
			foreach (DataRowView rowView in dataView)
				sortedDataTable.ImportRow(rowView.Row);

			DataContainer.Tables.Remove(currentDataTableName);
			DataContainer.Tables.Add(sortedDataTable);
		}
		
		
		/// <summary>
		/// Get the list of salary Distribution objects bound by a start and end index. 
		/// These indeces are into the list of salary Distribution 
		/// that was loaded during the Load() method execution into the data container. Typically, the start index
		/// is on a "page" boundary, where the "page" referes to a datagrid page.
		/// Note that the total number of salary Distribution objects returned may be less than the requested count.
		/// We maintain a Hashtable of ArrayList objects using the start index as the key. This allows us to retrieve the
		/// set of salary Distribution objects associated with that index, thereby directly supporting random access based the
		/// page index.
		/// If such an ArrayList does not exist for the current page index, we load them using the list of salary Distribution IDs.
		/// The start end end indeces into that list are computed based on the current page index and the datagrid page size.
		/// salary Distribution objects are loaded based on the salary Distribution  which falls within the compute start and end indeces, 
		/// and saved in an ArrayList. That ArrayList is then stored in the Hashtable with the current page index as its
		/// key.
		/// </summary>
		/// <param name="startIndex">Index within the salary Distribution list to begin at</param>
		/// <param name="count">Total number of salary Distribution objects to load</param>
		/// <returns>The list of salary Distribution objects</returns>
		public ArrayList GetSalaryDistributionList(int startIndex, int count)
		{
			ArrayList salaryDistributionList = salaryDistributionMap[startIndex] as ArrayList;
			if (salaryDistributionList == null)
			{
				salaryDistributionList = new ArrayList();
				DataTable dataTable = DataContainer.Tables[currentDataTableName];
				for (int i = startIndex, end = Math.Min(i + count, dataTable.Rows.Count); i < end; ++i)
					salaryDistributionList.Add(new SalaryDistribution(CachePolicy, dataTable.Rows[i]));
				salaryDistributionMap[startIndex] = salaryDistributionList;
			}
			return salaryDistributionList;
		}

		
		/// <summary>
		/// Private function used to compute totals for columns and add this table to datacontainer table collection
		///		TotalAnnualRate,
		///		TotalMonthlyRate,
		///		TotalCommittedSalary,
		///		TotalTotalCommited and
		///		TotalPaidToDate
		/// </summary>
		private void ComputeAndAddSalaryTotals()
		{
			DataTable totalsDataTable	= new DataTable(TotalsTableName);

//			totalsDataTable.Columns.Add(new DataColumn(AgreementConstants.TotalAnnualRate, typeof(Decimal)));
//			totalsDataTable.Columns.Add(new DataColumn(AgreementConstants.TotalMonthlyRate, typeof(Decimal)));
			totalsDataTable.Columns.Add(new DataColumn(AgreementConstants.TotalCommittedSalary, typeof(Decimal)));
			totalsDataTable.Columns.Add(new DataColumn(AgreementConstants.TotalCommittedFringe, typeof(Decimal)));
			totalsDataTable.Columns.Add(new DataColumn(AgreementConstants.TotalTotalCommitted, typeof(Decimal)));
			totalsDataTable.Columns.Add(new DataColumn(AgreementConstants.TotalPaidToDate, typeof(Decimal)));

			DataTable salariesDataTable = DataContainer.Tables[OriginalDataTableName];

			if (salariesDataTable.Rows.Count > 0)
			{
				DataRow totalsRow = totalsDataTable.NewRow();
				
//				object o = (salariesDataTable.Compute(SumAnnualRate, string.Empty));
//				totalsRow[AgreementConstants.TotalAnnualRate]			= (o == System.DBNull.Value)? 0 : Convert.ToDecimal(o);
//
//				o = (salariesDataTable.Compute(SumMonthlyRate, string.Empty));
//				totalsRow[AgreementConstants.TotalMonthlyRate]		= (o == System.DBNull.Value)? 0 : Convert.ToDecimal(o);

				object o = (salariesDataTable.Compute(SumCommittedSalary, string.Empty));
				totalsRow[AgreementConstants.TotalCommittedSalary]		= (o == System.DBNull.Value)? 0 : Convert.ToDecimal(o);

				o = (salariesDataTable.Compute(SumCommittedFringe, string.Empty));
				totalsRow[AgreementConstants.TotalCommittedFringe]			= (o == System.DBNull.Value)? 0 : Convert.ToDecimal(o);
				
				o = (salariesDataTable.Compute(SumTotalCommitted, string.Empty));
				totalsRow[AgreementConstants.TotalTotalCommitted]		= (o == System.DBNull.Value)? 0 : Convert.ToDecimal(o);

				o = (salariesDataTable.Compute(SumPaidToDate, string.Empty));
				totalsRow[AgreementConstants.TotalPaidToDate]			= (o == System.DBNull.Value)? 0 : Convert.ToDecimal(o);
								
				totalsDataTable.Rows.Add(totalsRow);
			}
			DataContainer.Tables.Add(totalsDataTable);
		}

        private bool IsDate(string sdate)
        {
            try
            {
                DateTime.Parse(sdate);
                return true;
            }
            catch
            {
            }
            return false;
        }
		#endregion
		
		#region Business Object Methods
		/// <summary>
		/// Loads the data container with data from repository.	 and calls private
		/// method ComputeAndAddSalaryTotals to compute and add the totals table to Datacontainer 
		/// Table Collection
		/// </summary>
		/// <param name="connectionString">The connection string to the data source.</param>
		/// <param name="args">arguments to be passed to the stored procedure.</param>
		public override void Load(string connectionString, params object[] args) 
		{
			if (args[(int)StoredPROCArguments.SearchCriteria] == null )
				throw new Exception("Salary Distributions load requires Search Criteria containing at least a domain user ID");

			if (args[(int)StoredPROCArguments.SessionID] == null)
				throw new Exception("Salary Distributions load requires Session Id and Search Criteria containing at least a domain user ID");

			string sessionId = (string) args[(int)StoredPROCArguments.SessionID];
			SearchCriteria commonSearchCriteria = (SearchCriteria) args[(int)StoredPROCArguments.SearchCriteria];

            if ((commonSearchCriteria[Constants.ProjectStartDateKey] != null) && !IsDate(commonSearchCriteria[Constants.ProjectStartDateKey]))
                throw new Exception("Project Start Date contains an invalid date.");

            if ((commonSearchCriteria[Constants.ProjectEndDateKey] != null) && !IsDate(commonSearchCriteria[Constants.ProjectEndDateKey]))
                throw new Exception("Project End Date contains an invalid date.");
					
            if ((commonSearchCriteria[Constants.BudgetStartDateKey] != null) && !IsDate(commonSearchCriteria[Constants.BudgetStartDateKey]))
                throw new Exception("Budget Start Date contains an invalid date.");

            if ((commonSearchCriteria[Constants.BudgetEndDateKey] != null) && !IsDate(commonSearchCriteria[Constants.BudgetEndDateKey]))
                throw new Exception("Budget End Date contains an invalid date.");

            if ((commonSearchCriteria[Constants.PayFromDateKey] != null) && !IsDate(commonSearchCriteria[Constants.PayFromDateKey]))
                throw new Exception("Pay From Date contains an invalid date.");

            if ((commonSearchCriteria[Constants.PayToDateKey] != null) && !IsDate(commonSearchCriteria[Constants.PayToDateKey]))
                throw new Exception("Pay To Date contains an invalid date.");

            if ((commonSearchCriteria[Constants.ProjectStartDateKey] != null) && (commonSearchCriteria[Constants.ProjectEndDateKey] != null))
                if (DateTime.Compare(DateTime.Parse(commonSearchCriteria[Constants.ProjectStartDateKey]),DateTime.Parse(commonSearchCriteria[Constants.ProjectEndDateKey])) > 0)
                    throw new Exception("Project Start Date cannot be after Project End Date.");

            if ((commonSearchCriteria[Constants.BudgetStartDateKey] != null) && (commonSearchCriteria[Constants.BudgetEndDateKey] != null))
                if (DateTime.Compare(DateTime.Parse(commonSearchCriteria[Constants.BudgetStartDateKey]),DateTime.Parse(commonSearchCriteria[Constants.BudgetEndDateKey])) > 0)
                    throw new Exception("Budget Start Date cannot be after Budget End Date.");

            if ((commonSearchCriteria[Constants.PayFromDateKey] != null) && (commonSearchCriteria[Constants.PayToDateKey] != null))
                if (DateTime.Compare(DateTime.Parse(commonSearchCriteria[Constants.PayFromDateKey]),DateTime.Parse(commonSearchCriteria[Constants.PayToDateKey])) > 0)
                    throw new Exception("Pay From Date cannot be after Pay To Date.");

			if(DataContainer != null)
				DataContainer.Reset();

         string storedProc = GetSalariesOverviewStoreProc;

         string cumLevel = "1";
         int budgetYear = 0;
         if ((bool)args[(int)StoredPROCArguments.IsDetail])
         {
             storedProc = GetSalaryDetailsStoreProc;
             cumLevel = args[(int)StoredPROCArguments.CumLevel].ToString();
             budgetYear = args[(int)StoredPROCArguments.BudgetYear] != null ? Convert.ToInt32(args[(int)StoredPROCArguments.BudgetYear]) : 0;

         }

			DataContainer  = DataAccess.GetDataSet(connectionString, storedProc,
					DataAccess.BuildSqlParameterArray(
					SessionID,				SqlDbType.VarChar, 	sessionId,	
					DomainUserID,			SqlDbType.Int,		commonSearchCriteria[InsightUtilities.Constants.DomainUserIDKey],	
					IncludeProgramProjects,	SqlDbType.Bit,		commonSearchCriteria[InsightUtilities.Constants.IncludeProgramProjectsKey],			
					FundNumber,				SqlDbType.VarChar, 	commonSearchCriteria[InsightUtilities.Constants.FundIDKey],	
					FolderNumber,			SqlDbType.VarChar,	commonSearchCriteria[InsightUtilities.Constants.FolderNumberKey],		
					BicsNumber,				SqlDbType.VarChar,	commonSearchCriteria[InsightUtilities.Constants.BicsNumberKey],	
					WalkerNumber,			SqlDbType.VarChar,	commonSearchCriteria[InsightUtilities.Constants.WalkerNumberKey],		
					SponsorNumber,			SqlDbType.VarChar,	commonSearchCriteria[InsightUtilities.Constants.SponsorNumberKey],		
					SponsorName,			SqlDbType.VarChar,	commonSearchCriteria[InsightUtilities.Constants.SponsorNameKey],	
					SponsorTypeId,			SqlDbType.Int,		commonSearchCriteria[InsightUtilities.Constants.SponsorTypeIDKey],	
					AgreementTypeId,		SqlDbType.Int,		commonSearchCriteria[InsightUtilities.Constants.AgreementTypeIDKey],	
					ActivityCode,			SqlDbType.VarChar,	commonSearchCriteria[InsightUtilities.Constants.ActivityCodeKey],		
					LocationId,				SqlDbType.Int, 		commonSearchCriteria[InsightUtilities.Constants.LocationIDKey],
					BudgetStartDate,		SqlDbType.DateTime, commonSearchCriteria[InsightUtilities.Constants.BudgetStartDateKey],			
					BudgetEndDate,			SqlDbType.DateTime, commonSearchCriteria[InsightUtilities.Constants.BudgetEndDateKey],			
					ProjectStartDate,		SqlDbType.DateTime, commonSearchCriteria[InsightUtilities.Constants.ProjectStartDateKey],				
					ProjectEndDate,			SqlDbType.DateTime, commonSearchCriteria[InsightUtilities.Constants.ProjectEndDateKey],			
					PiId,					SqlDbType.Int,		commonSearchCriteria[InsightUtilities.Constants.PIIDKey],
					//LabId,				SqlDbType.Int, 		null,//can be removed	
					ChiefCodeList,			SqlDbType.VarChar, 	commonSearchCriteria.COSIds,			
					SponsorGroupTypeId,		SqlDbType.Int,		commonSearchCriteria[InsightUtilities.Constants.ViewingOptionKey],			
					BudgetEndingDays,		SqlDbType.Int,		commonSearchCriteria[InsightUtilities.Constants.DaysBudgetEndingKey],			
					ProjectEndingDays,		SqlDbType.Int,		commonSearchCriteria[InsightUtilities.Constants.DaysProjectEndingKey],			
					DaysFundCreated,		SqlDbType.Int,		commonSearchCriteria[InsightUtilities.Constants.DaysFundCreatedKey],
					LowBalance,				SqlDbType.Decimal,	commonSearchCriteria[InsightUtilities.Constants.BalanceLessThanKey],		
					HighBalance,			SqlDbType.Decimal,	commonSearchCriteria[InsightUtilities.Constants.BalanceGreaterThanKey],		
					AgreementStatusTypeList,	SqlDbType.VarChar, ((bool)args[(int)StoredPROCArguments.IgnoreAgreementStatus])? " " : commonSearchCriteria[InsightUtilities.Constants.AgreementStatusIDKey],
					FundStatusTypeId,		SqlDbType.Int,		((bool)args[(int)StoredPROCArguments.IgnoreFundStatus]) ? "0" : commonSearchCriteria[InsightUtilities.Constants.FundStatusIDKey],			
					Currentdatetime,		SqlDbType.DateTime, null,				
					FirstName,				SqlDbType.VarChar,	commonSearchCriteria[InsightUtilities.Constants.FirstNameKey],		
					LastName,				SqlDbType.VarChar,	commonSearchCriteria[InsightUtilities.Constants.LastNameKey],		
					LastGLFeedRunDate,		SqlDbType.DateTime, args[(int)StoredPROCArguments.LastGLFeedRunDate].ToString(),					
					EmployeeID,				SqlDbType.VarChar,	commonSearchCriteria[InsightUtilities.Constants.EmployeeIdKey],		
					PayDateFrom,			SqlDbType.DateTime,	commonSearchCriteria[InsightUtilities.Constants.PayFromDateKey],		
					PayDateTo,				SqlDbType.DateTime,	commonSearchCriteria[InsightUtilities.Constants.PayToDateKey],
                    IncomingFundNumber, SqlDbType.VarChar, args[(int)StoredPROCArguments.IncomingFundNumber].ToString(),
                    Cumulative, SqlDbType.Int, cumLevel,
                    BudgetYear, SqlDbType.Int, budgetYear				
				));

//			if(DataContainer.Tables.Count !=1)
//				throw new Exception("Error in Executing Salary Distributions StoreProc");

			DataContainer.Tables[0].TableName = OriginalDataTableName;
			if (DataContainer.Tables.Count == 2)
				DataContainer.Tables[1].TableName = Constants.AgreementsConstants.FundInfoTableName;

			DataView dataView = new DataView(DataContainer.Tables[OriginalDataTableName]);
			dataView.Sort = String.Format("{0} {1}",SortColumn, SortAscending ? Constants.SortViewAscending: Constants.SortViewDescending);
			dataView.RowStateFilter = DataViewRowState.CurrentRows;

			DataTable dataTable = dataView.Table.Clone();
			dataTable.TableName = OriginalDataTableName;

			foreach (DataRowView dataRowView in dataView)
				dataTable.ImportRow(dataRowView.Row);

			DataContainer.Tables.Remove(OriginalDataTableName);
			DataContainer.Tables.Add(dataTable);

			ComputeAndAddSalaryTotals();
				
		}
		#endregion
	}
}
